/* DATABASE */
IF NOT EXISTS (SELECT * FROM sys.databases WHERE [name] = 'LabManager')
BEGIN
    CREATE DATABASE [LabManager]
END
GO

USE [LabManager]
GO

/* DROP TABLES */
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductMachineCompatibility'))
    DROP TABLE [ProductMachineCompatibility]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductStateTrail'))
    DROP TABLE [ProductStateTrail]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginTrail'))
    DROP TABLE [LoginTrail]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LabEnviromentProductType'))
    DROP TABLE [LabEnviromentProductType]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Analysis'))
    DROP TABLE [Analysis]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductObject'))
    DROP TABLE [ProductObject]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Product'))
    DROP TABLE [Product]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductType'))
    DROP TABLE [ProductType]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'User'))
    DROP TABLE [User]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'UserGroup'))
    DROP TABLE [UserGroup]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Department'))
    DROP TABLE [Department]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'PackageInsert'))
    DROP TABLE [PackageInsert]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Manufacturer'))
    DROP TABLE [Manufacturer]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'State'))
    DROP TABLE [State]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Machine'))
    DROP TABLE [Machine]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LoginState'))
    DROP TABLE [LoginState]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'MachineType'))
    DROP TABLE [MachineType]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'ProductCategory'))
    DROP TABLE [ProductCategory]
GO

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LabEnvironment'))
    DROP TABLE [LabEnvironment]
GO

/* DROP VIEWS */
IF EXISTS(select * FROM sys.views where name = 'V_UserList')
	DROP VIEW [V_UserList]
GO

IF EXISTS(select * FROM sys.views where name = 'V_MachineList')
	DROP VIEW [V_MachineList]
GO

IF EXISTS(select * FROM sys.views where name = 'V_ProductType')
	DROP VIEW [V_ProductType]
GO

IF EXISTS(select * FROM sys.views where name = 'V_ProductObject')
	DROP VIEW [V_ProductObject]
GO

IF EXISTS(select * FROM sys.views where name = 'V_Product')
	DROP VIEW [V_Product]
GO

/* CREATE ENUM TABLES */
CREATE TABLE [PackageInsert]
(
	[PackageInsertID]	INT	IDENTITY(1,1)		NOT NULL,
	[Description]		VARCHAR(1000)			NOT NULL,
	[Version]			VARCHAR(100)			NOT NULL,
	[FilePath]			VARCHAR(255)			NOT NULL,
	[ReleaseDate]		DATE					NOT NULL
		
	CONSTRAINT			[PK_PackageInsertID]	PRIMARY KEY ([PackageInsertID])
)

CREATE TABLE [State]
(
	[StateID]			INT	IDENTITY(1,1)		NOT NULL,
	[StateName]			VARCHAR	(100)			NOT NULL,
		
	CONSTRAINT			[PK_StateID]			PRIMARY KEY ([StateID])
)

CREATE TABLE [LoginState]
(
	[LoginStateID]		INT IDENTITY(1,1)		NOT NULL,
	[LoginStateName]	VARCHAR	(100)			NOT NULL,
		
	CONSTRAINT			[PK_LoginStateID]		PRIMARY KEY ([LoginStateID])
)

CREATE TABLE [Manufacturer]
(
	[ManufacturerID]	INT	IDENTITY(1,1)		NOT NULL,
	[ManufacturerName]	VARCHAR(100)			NOT NULL,

	CONSTRAINT			[PK_ManufacturerID]		PRIMARY KEY ([ManufacturerID])
)

CREATE TABLE [MachineType]
(
	[MachineTypeID]		INT	IDENTITY(1,1)		NOT NULL,
	[Identification]	VARCHAR(100)			NOT NULL,
	[Manufacturer]		VARCHAR(100)			NOT NULL,
	[Description]		VARCHAR(1000)			NOT NULL,

	CONSTRAINT			[PK_MachineTypeID]		PRIMARY KEY ([MachineTypeID])
)

CREATE TABLE [ProductCategory]
(
	[ProductCategoryID]		INT	IDENTITY(1,1)	NOT NULL,
	[ProductCategoryName]	VARCHAR(100)		NOT NULL,

	CONSTRAINT			[PK_ProductCategoryID]	PRIMARY KEY ([ProductCategoryID])
)

CREATE TABLE [UserGroup]
(
	[UserGroupID]		INT	IDENTITY(1,1)		NOT NULL,
	[UserGroupName]		VARCHAR(100)			NOT NULL,
	
	CONSTRAINT			[PK_UserGroupID]		PRIMARY KEY ([UserGroupID]),
)

CREATE TABLE [Department]
(
	[DepartmentID]		INT	IDENTITY(1,1)		NOT NULL,
	[DepartmentName]	VARCHAR(100)			NOT NULL,

	CONSTRAINT			[PK_DepartmentID]		PRIMARY KEY ([DepartmentID]),
)

CREATE TABLE [LabEnvironment]
(
	[LabEnvironmentID]		INT	IDENTITY(1,1)	NOT NULL,
	[LabEnvironmentName]	VARCHAR(100)		NOT NULL,
	
	CONSTRAINT			[PK_LabEnvironmentID]	PRIMARY KEY ([LabEnvironmentID]),
)

/* CREATE REFERENCE TABLES */
CREATE TABLE [User]
(
	[UserID]			INT	IDENTITY(1,1)		NOT NULL,
	[UserGroupID]		INT						NOT NULL,
	[DepartmentID]		INT						NOT NULL,
	[FirstName]			VARCHAR	(100)			NOT NULL,
	[LastName]			VARCHAR	(100)			NOT NULL,
	[MailAddress]		VARCHAR	(100)			NOT NULL,
	[Login]				VARCHAR	(100) UNIQUE	NOT NULL,
	[Password]			VARCHAR	(100)			NOT NULL,
	[Active]			BIT						NOT NULL,
	
	CONSTRAINT			[PK_UserID]				PRIMARY KEY ([UserID]),
	CONSTRAINT			[FK_User_UserGroup]		FOREIGN KEY ([UserGroupID])		REFERENCES [UserGroup]([UserGroupID]),
	CONSTRAINT			[FK_User_Department]	FOREIGN KEY ([DepartmentID])	REFERENCES [Department]([DepartmentID])
)

CREATE TABLE [ProductType]
(
	[ProductTypeID]			INT	IDENTITY(1,1)	NOT NULL,
	[ManufacturerID]		INT					NOT NULL,
	[ProductCategoryID]		INT					NOT NULL,
	[REF]					VARCHAR(100)		NOT NULL,
	[InventoryCode]			VARCHAR(100)		NOT NULL,
	[Tag]					VARCHAR(100)		NOT NULL,
	[ProductDescription]	VARCHAR(1000)		NOT NULL,

	CONSTRAINT			[PK_ProductTypeID]					PRIMARY KEY ([ProductTypeID]),
	CONSTRAINT			[FK_ProductType_Manufacturer]		FOREIGN KEY ([ManufacturerID])		REFERENCES [Manufacturer]([ManufacturerID]),
	CONSTRAINT			[FK_ProductType_ProductCategory]	FOREIGN KEY ([ProductCategoryID])	REFERENCES [ProductCategory]([ProductCategoryID]),
)

CREATE TABLE [Product]
(
	[ProductID]			INT	IDENTITY(1,1)		NOT NULL,
	[ProductTypeID]		INT						NOT NULL,
	[PackageInsertID]	INT						NOT NULL,
	[OrderNumber]		VARCHAR(100)			NOT NULL,
	[LOT]				VARCHAR(100)			NOT NULL,
	[TagDate]			DATE					NOT NULL,
	[ExpirationDate]	DATE					NOT NULL,

	CONSTRAINT			[PK_ProductID]				PRIMARY KEY ([ProductID]),
	CONSTRAINT			[FK_Product_ProductType]	FOREIGN KEY ([ProductTypeID])		REFERENCES [ProductType]([ProductTypeID]),
	CONSTRAINT			[FK_User_PackageInsert]		FOREIGN KEY ([PackageInsertID])		REFERENCES [PackageInsert]([PackageInsertID]),
)

CREATE TABLE [ProductObject]
(
	[ProductObjectID]	INT	IDENTITY(1,1)		NOT NULL,
	[ProductID]			INT						NOT NULL,
	[BarcodeNumber]		VARCHAR(100)			NOT NULL,

	CONSTRAINT			[PK_ProductObjectID]			PRIMARY KEY ([ProductObjectID]),
	CONSTRAINT			[FK_ProductObject_Product]		FOREIGN KEY ([ProductID])			REFERENCES [Product]([ProductID]),
)

CREATE TABLE [Machine]
(
	[MachineID]			INT	IDENTITY(1,1)		NOT NULL,
	[MachineTypeID]		INT						NOT NULL,
	[Name]				VARCHAR(100)			NOT NULL,

	CONSTRAINT			[PK_MachineID]				PRIMARY KEY ([MachineID]),
	CONSTRAINT			[FK_Machine_MachineType]	FOREIGN KEY ([MachineTypeID])			REFERENCES [MachineType]([MachineTypeID])
)

CREATE TABLE [ProductMachineCompatibility]
(
	[ProductMachineCompatibilityID]	INT	IDENTITY(1,1)	NOT NULL,
	[ProductTypeID]					INT					NOT NULL,
	[MachineTypeID]					INT					NOT NULL,
	
	CONSTRAINT			[PK_ProductMachineCompatibilityID]				PRIMARY KEY ([ProductMachineCompatibilityID]),
	CONSTRAINT			[FK_ProductMachineCompatibility_ProductType]	FOREIGN KEY ([ProductTypeID])	REFERENCES [ProductType]([ProductTypeID]),
	CONSTRAINT			[FK_ProductMachineCompatibility_MachineType]	FOREIGN KEY ([MachineTypeID])	REFERENCES [MachineType]([MachineTypeID])
)

CREATE TABLE [Analysis]
(
	[AnalysisID]		INT	IDENTITY(1,1)		NOT NULL,
	[ProductObjectID]	INT						NOT NULL,
	[MachineID]			INT						NOT NULL,

	CONSTRAINT			[PK_AnalysisID]					PRIMARY KEY ([AnalysisID]),
	CONSTRAINT			[FK_Analysis_ProductObject]		FOREIGN KEY ([ProductObjectID])	REFERENCES [ProductObject]([ProductObjectID]),
	CONSTRAINT			[FK_Analysis_Machine]			FOREIGN KEY ([MachineID])		REFERENCES [Machine]([MachineID])
)

CREATE TABLE [LabEnviromentProductType]
(
	[LabEnviromentProductTypeID]	INT	IDENTITY(1,1)	NOT NULL,
	[LabEnvironmentID]				INT					NOT NULL,
	[ProductTypeID]					INT					NOT NULL,

	CONSTRAINT			[PK_LabEnviromentProductTypeID]					PRIMARY KEY ([LabEnviromentProductTypeID]),
	CONSTRAINT			[FK_LabEnvironmentProductType_LabEnvironment]	FOREIGN KEY ([LabEnvironmentID])	REFERENCES [LabEnvironment]([LabEnvironmentID]),
	CONSTRAINT			[FK_LabEnvironmentProductType_ProductType]		FOREIGN KEY ([ProductTypeID])		REFERENCES [ProductType]([ProductTypeID])
)

CREATE TABLE [ProductStateTrail]
(
	[ProductStateTrailID]		INT	IDENTITY(1,1)	NOT NULL,
	[ProductObjectID]			INT					NOT NULL,
	[UserID]					INT					NOT NULL,
	[StateID]					INT					NOT NULL,
	[Timestamp]					DATETIME			NOT NULL	DEFAULT CURRENT_TIMESTAMP,
		
	CONSTRAINT			[PK_ProductStateTrailID]				PRIMARY KEY ([ProductStateTrailID]),
	CONSTRAINT			[FK_ProductStateTrail_ProductObject]	FOREIGN KEY ([ProductObjectID])		REFERENCES	[ProductObject]([ProductObjectID]),
	CONSTRAINT			[FK_ProductStateTrail_User]				FOREIGN KEY ([UserID])				REFERENCES	[User]([UserID]),
	CONSTRAINT			[FK_ProductStateTrail_State]			FOREIGN KEY ([StateID])				REFERENCES	[State]([StateID])
)


CREATE TABLE [LoginTrail]
(
	[LoginTrailID]			INT	IDENTITY(1,1)		NOT NULL,
	[UserID]				INT						NOT NULL,
	[LoginStateID]			INT						NOT NULL,
	[Timestamp]				DATETIME				NOT NULL,
		
	CONSTRAINT			[PK_LoginTrailID]					PRIMARY KEY ([LoginTrailID]),
	CONSTRAINT			[FK_LoginTrail_Product]				FOREIGN KEY ([UserID])			REFERENCES	[User]([UserID]),
	CONSTRAINT			[FK_LoginTrail_State]				FOREIGN KEY ([LoginStateID])	REFERENCES	[LoginState]([LoginStateID])
)
GO

/* CREATE VIEW */
CREATE VIEW [V_UserList] AS
(
	SELECT	[usr].[UserID],
			[usr].[Login],
			[usr].[Firstname],
			[usr].[Lastname],
			[dep].[DepartmentName],
			[grp].[UserGroupName],
			[usr].[Active],
			[usr].[MailAddress],
			[usr].[Password]
	FROM [User] [usr]
	INNER JOIN [Department] [dep] ON [dep].[DepartmentID] = [usr].[DepartmentID]
	INNER JOIN [UserGroup] [grp] ON [grp].[UserGroupID] = [usr].[UserGroupID]
)
GO

CREATE VIEW [V_MachineList] AS
(
	SELECT	[mac].[MachineID],
			[mac].[Name],
			[mat].[Identification]
	FROM [Machine] [mac]
	INNER JOIN [MachineType] [mat] ON [mat].[MachineTypeID] = [mac].[MachineTypeID]
)
GO

CREATE VIEW [V_ProductType] AS
(
	SELECT	[prt].[ProductTypeID],
			[man].[ManufacturerName],
			[prc].[ProductCategoryName],
			[prt].[REF],
			[prt].[InventoryCode],
			[prt].[Tag],
			[prt].[ProductDescription]
	FROM [ProductType] [prt]
	INNER JOIN [Manufacturer] [man] ON [man].[ManufacturerID] = [prt].[ManufacturerID]
	INNER JOIN [ProductCategory] [prc] ON [prc].[ProductCategoryID] = [prt].[ProductCategoryID]
)
GO

CREATE VIEW [V_Product] AS
(
	SELECT	[pro].[ProductID],
			[prt].[ProductDescription],
			[pai].[Description],
			[pro].[OrderNumber],
			[pro].[LOT],
			[pro].[TagDate],
			[pro].[ExpirationDate]
	FROM [Product] [pro]
	INNER JOIN [ProductType] [prt] ON [prt].[ProductTypeID] = [pro].[ProductTypeID]
	INNER JOIN [PackageInsert] [pai] ON [pai].[PackageInsertID] = [pro].[PackageInsertID]
)
GO

CREATE VIEW [V_ProductObject] AS
(
	SELECT	[pro].[ProductObjectID],
			[pro].[BarcodeNumber],
			[vpr].[ProductID],
			[vpr].[ProductDescription],
			[vpr].[Description],
			[vpr].[OrderNumber],
			[vpr].[LOT],
			[vpr].[TagDate],
			[vpr].[ExpirationDate],
			[sta].[StateName],
			[sta].[StateID]
	FROM [ProductObject] [pro]
	INNER JOIN [V_Product] [vpr] ON [vpr].[ProductID] = [pro].[ProductID]
	INNER JOIN [ProductStateTrail] [pst] ON [pst].[ProductObjectID] = [pro].[ProductObjectID]
	INNER JOIN [State] [sta] ON [sta].[StateID] = [pst].[StateID]
)
GO

/* INSERT VALUES */
INSERT INTO [UserGroup]
VALUES	('User'), ('Poweruser'), ('Supervisor'), ('Administrator')

INSERT INTO [Department]
VALUES	('Unknown'), ('Information Technology'), ('Laboratory')

INSERT INTO [LoginState]
VALUES	('Login'), ('Logout')

INSERT INTO [User]
VALUES	(4, 2, 'Alexandros', 'Theodoracatos', 'theodoracatos@gmail.com', 'ath', '151172042000010093196228172084191045221070063153', 1),
		(1, 1, 'Chryssoula', 'Delidimou', 'delidimou@gmail.com', 'cth', '151172042000010093196228172084191045221070063153', 1),
		(2, 3, 'Sinisa', 'Dimitrijevic', 'sini@gmail.com', 'sdi', '151172042000010093196228172084191045221070063153', 1)

INSERT INTO [State]
VALUES ('In stock'), ('In Use'), ('Used'), ('Expired')

--INSERT INTO [MachineType]
--VALUES ('Unknown', 'Unknown', 'Unknown')

INSERT INTO [Manufacturer]
VALUES ('Roche') , ('Novartis'), ('Sandoz')

INSERT INTO [ProductCategory]
VALUES ('Category A')